import json

import requests

from app.modules1.PublicApproach import get_last_month_range, _get_vendor_ids_from_db, get_token_result, \
    _get_sku_ids_from_db
from app.modules1.common.encryptionUtils import getResult1
from config import DB


# 1.从采购入库明细表内获取实销实结供应商全部货品
# 2.从全部货品中查询销售货品明细账中全部的销量
# 3.最后以供应商明细和货品明细去供应商货品价目中查询货品单价

def _get_SalesDetailList():
    """获取实销实结销售货品明细账"""
    # 获取token
    token = get_token_result()
    # 渠道id
    shopId = "2196547704831476736,2196472596733494272,1900063326934532992,1143418968494670080,1101949325263504512,1414596840864875264,1083109264100426624,1086958714925515392,1219614629365252736,1977100255194121344,1800057316234789632,1926812876169611264,1579689381010276864,2210264775810811520,2210264381479126656,2210263953282238208,2210261840647487744,2209677764108420608,2209677060889019264,2209454970934330880,2208734369617348352,2208729100531729792,2208728713780954880,2208728467770246912,2208728175098103552,2208727822812120832,2204356398997735168,2203806156512364416,2203652395374544768,2202953621054587648,2200931928813797632,2200930232903303808,2200182178518860672,2193691560617771904,2193587915603216512,2185032747339022720,2183590935726850816,2182790239932975488,2182778287481357952,2182777842811865472,2181395521896384128,2180453050062538880,2180432194544993024,2177773842724619392,2175341148349825408,2174881101903726208,2174881102574814848,2174881103153628800,2174881093649335936,2174881096014923392,2174881096627291776,2174881097231271552,2174881097810085504,2174881098959324800,2174881099521361536,2174881100108564096,2174881100729321088,2174881101333300864,2174881090109343360,2174881090998535808,2174881091602515584,2174881092357490304,2174881093003413120,2174860079490041472,2174832741586994816,2171256263775520256,2171256022839265024,2171255747069772416,2168880568685365248,2168302412113021056,2166098787597943808,2166098445719209728,2166098146094909184,2166097790115940096,2163137267154945792,2162529795037266176,2153121513952576128,2148710166569060480,2145170469658985601,2144989905328342144,2122733042855642752,2118880635105118080,2117512826659866496,2117496761483461888,2116853259334125568,2113956753731127168,2112585661766567808,2112364836803184512,2111585790972560128,2108784274217763712,2108726517544421120,2108726518198732544,2108726519540909824,2108726520337827584,2108726520958584576,2108726509466191616,2108726510053394176,2108726510816757504,2108726511513011968,2108726512100214528,2108726512679028480,2108726513408837376,2108726514088314624,2108726515002672896,2108726515707315968,2108726516294518528,2108726516915275520,2108726508375672576,2105050697803040896,2104344765290939776,2103752472676599040,2103751849971451008,2103750734940898432,2103750308415735040,2103748889625298176,2103742136577754368,2103741030111021312,2103739924946420992,2103731020245141760,2103730172777629952,2103728032550127872,2103726689264501888,2103725823393433856,2103724231923501312,2103723680390251776,2103723048535131392,2103717919494931584,2103716926486120704,2103716106138978560,2103715316921960704,2103057624436901120,2101570176968099456,2100022967670802304,2098537459848480640,2094457208749361664,2094312497391501952,2094295192214637056,2092179397060461056,2091452220091597824,2089194434358805504,2086955279994226944,2083501665192576384,2083501324766478720,2083499867228669312,2083498091392959616,2083497556006831232,2083497208333534592,2083463103238800512,2082119027792971264,2082107936962676224,2078511718047023744,2078511472706388608,2078511175531561600,2078510875180074240,2078510495998215424,2078510172114060544,2078509568861471360,2078509186399666816,2078508815841297024,2078508533010006656,2078508187780449536,2078507483981021824,2078507112281801344,2078506558318511360,2078460833363264128,2078252934350832896,2076179465309585792,2073951852469027584,2073891386114869248,2071950667345495296,2071942653148464512,2071189193307783808,2068137947819084160,2068136692608730496,2064943325541893632,2058143401155363840,2050806952688421120,2050221104687284992,2049980462457358592,2043617314778023168,2037830584998101376,2036935729903174528,2036318429155492608,2026787623177946368,2026306506570204032,2023971525086086784,2021880450899641728,2018117789249602432,2017836838728663936,2015235392111804672,2015232436320666880,2012824299193109120,2012821672989360640,2005409470043029888,2005399677387997824,2005033103704065920,2002124796371732224,1988543689976512896,1986448812687557760,1986446009156043904,1986444135510115456,1970328742546702592,1967396796225095040,1967395890314151296,1966736339793413504,1966721846391735424,1964565695475942912,1961032214540125568,1960867993905662080,1957246104458429440,1926967658585916288,1916596105592505728,1915995400023442688,1908878596202988928,1910079785883861888,1901400835804169216,1895704827962196224,1878930379750834432,1858676755176621312,1855138128719348096,1849447833779602304,1829804197811586176,1811056171992252928,1809384805940003456,1800059267879993728,1793567039270683520,1693406127751168512,1687807540230095232,1612402885736464768,1600002406283903616,1573979385258770688,1549315538513659008,2208998159009253120,2204494509763266304,2203622277527765504,2202413764811588608,2200807933904879872,2200768132633494144,2199314377791636608,2198755863037903232,2198742322155258240,2196536607073174528,2195800648178271232,2194319266485798656,2194311373434782720,2194237544012317696,2193673207146152576,2193671133476488064,2193452640686113408,2192771934993581056,2191341531515388928,2191360233283191296,2169595653540807680,2190667358882563072,2189137873580492288,2188534232277485184,2188391764690012032,2187892022787835008,2187718962985010560,2187689943376987264,2186995594263068928,2185594659263545600,2179182304206652416,2184352013903365760,2182020779208411264,2180494427385398784,2170477703902791040,2175431386033096064,2175346451639764736,2170433603996222080,2173204274144805120,2171278463604785792,2170283927013754624,2169589266423613824,2168316287873516928,2167682121687008640,2161153492702528000,2154507393965720064,2158905670754436352,2157411032159814400,2155360891726364928,2153739127159162112,2152531935483561472,2150730606764557696,2121103446570861312,2133534067615138432,2034133175847879680,2132705685315420416,2132038953308325760,2128983770918716160,2127560398379844736,2125486380426461824,2124671750101958912,2125447991353311872,2125374383376893952,2125342564829496704,2125316641740686720,2121102908614771200,2123633319370491008,2122513269664319104,2122437568328763136,2121663364159374080,2121207704296163200,2121190566065800448,2121100890827391488,2120593058829861632,2120359605398996864,2119587063505552640,2111814573343081600,2117589404840002816,2117395753851880704,2115921767782286720,2115395733005665664,2113791986405016064,2113764728008213760,2113453585226138880,2113401905486923648,2109867167575671936,2112287060667859200,2111937477198676224,2111758088032945536,2111709988416979712,2111667649736672000,2111667614537122560,2111667603733939584,2111667592039826176,2111667594932945664,2111667586394292992,2111667586485617408,2111667586763785600,2111667579112981248,2111667579239204224,2111033427803144960,2110830307475096320,2109158586654982528,2109168691723437312,2108706474678223488,2108674986360373888,2107523767570793088,2102350017692664576,2106583272644216960,2105852424288634240,2105071801924388736,2104377338805684096,2103033579724245248,2102185683714901632,2101644907300915072,2101644912535438720,2101644904121664896,2101635388931999104,2100877252106912256,2100322758658884096,2099270258197332736,2099220591732392704,2098751282110957440,2098043594377921280,2097293558477031552,2097276882394089600,2095278581017478528,2096626607321416960,2096493313576010752,2095707213258981760,2095019096619746304,2094336611997090432,2093511526791939072,2093475767626498816,2082782220254445824,2092042218161013760,2088527844065739648,2091361528078729728,2088447070083810816,2089186678235628800,2088446061571801984,2089098396402255104,2087890945001194240,2088375599342356224,2087720907593024256,2087078176197968512,2082782765611024896,2087100654996489600,2086977069688719616,2086432870135661696,2086204470015722112,2086200219265796224,2084190207629755008,2083511740737684224,2078443856982771200,2081983439554544896,2081971303422494208,2081844015344715008,2068330167132128896,2078450022827329024,2081142239102436224,2080684037445452672,2079891050845078912,2079891397084414592,2080506612820641280,2079880045210272256,2079842788147364352,2079234207288394496,2078940476582724864,2078266760289059072,2068256275783550720,2077550503536690176,2076964082483594496,2076304539746468992,2076100323613475712,2075481737603711744,2075386385445815040,2075343154090771584,2074726790230573824,2074671207180730752,2074149504329875840,2073407918313111936,2073408293661376896,2073984545147716352,2073980562772494336,2073870504337213440,2073868544874809344,2073497559322132864,2072698559333794432,2068865467267154304,2072461357549618048,2072478783400477568,2071907136028083200,2071169865262564352,2071108561747640960,2071108104751055488,2069086964584809344,2070388178165760640,2069873192796488320,2068899239755155328,2068193512590738048,2067007597410419456,2067007560803484800,2067007520889537280,2067007524338205824,2067007512122393472,2067007514228982528,2067007515435893632,2067007518062576384,2067007520024462208,2066825807747416576,2066036256715736960,2065938678607153024,2065899062521299456,2063856768678954880,2063766877232826240,2048014602595631744,2060859243639506432,2060803335638647296,2060389041513595392,2060233826649377024,2057315690125198464,2053758541910934528,2058641371558707456,2057913996769690496,2057445010636604160,2057441071413396224,2057365880541446528,2057266138885982208,2057169917885448576,2057169738976789248,2043447258464552192,2052920543082349568,2055734725760583936,2054992297601171840,2054992298255483264,2054301172757760256,2052286662433277568,2051507827231229312,2048085536816301312,2049328332560204544,2046677327242069376,2037736483091645184,2048624327913898880,2047330911431557888,2047098155796695040,2047048314789988352,2046441683644449024,2046441680414834944,2046308938948609280,2046308938956997888,2045047304224670976,2045025405544334208,2045025405552722816,2045025407800869760,2044942401216348672,2044953534052763520,2044863926485550336,2043053869668991232,1708816102065963392,2043040375403087104,2042915348895662976,2037086809343034368,2039251328073336192,2032596570589004032,2017450516572176768,2026922430136123776,2038380329260877056,2035073437818946176,2034757143847831808,2031292579459236864,2031849583978348928,2033310820154410240,2030775292473541376,2031319661182583808,2031315909507023872,2029168289892533376,2018223727748481664,2018898318532084608,2016626099303417984,2015891257929105920,2014694391565583616,2013136994741060096,2007917220977639680,2005779116930761216,2005887337501622528,2002169026327676800,2001383062137341312,2000880978232672896,1992209019762116224,1998519792402893056,1998490729917089664,1997786162221351552,1997784348784232192,1230498174662509184,1996492152192042752,1996492144298362624,1944161664144605824,1949445873177690880,1993562553997231104,1987871876967203072,1985871951226209280,1985687543891231744,1903148108963479936,1984260501345404160,1984206071881696128,1978350797099892992,1977470615556620544,1967429659201372800,1965935546563200128,1965270019846603520,1964665642349888000,1963876949099938304,1962198534409356800,1960803969769080192,1960093983143003904,1958050047543018624,1956156376889393408,1950822348546802048,1955112647551518592,1952960975295383424,1952825472274203648,1952272418251606272,1952171611401357568,1952171575766550784,1950587112344716032,1945212662536995712,1947211887850554880,1946568935663535232,1942182909710140288,1945731006390929024,1945008249999001216,1944795822790968320,1944167950080346368,1941636613432804736,1939943387681227008,1939935777165575552,1939345475119613184,1939997826520122752,1939935547430339072,1935776228846732416,1938643988303316480,1938584804157294080,1932574745462441984,1932574736587294720,1932574690107000576,1932489015785883392,1932488955110688512,1931840497889118080,1931840335628273536,1931839507613943680,1931239469753894528,1930322222864728832,1930322222873117440,1929593603890546944,1917491087293514368,1928953937038346624,1928204067321807744,1928197856044123008,1927489587022465024,1927420736264407424,1927420725157890432,1926995303207534848,1926877751575119872,1926769548447254528,1926769535461689344,1925475789074400512,1925246854290015488,1924717701182588160,1924525361579591040,1924525361730585984,1924525364976977280,1924525347345466624,1924525348712809728,1924525339074299136,1924525341356000512,1924525327877734912,1924525329091855616,1924525330635359488,1924525333823030528,1924034432734529408,1923980136907212672,1923910753445644544,1923910759779043584,1923910729388065024,1923910720872679680,1923910723742531840,1923910705034987776,1923910705043376384,1923910709111851264,1923874317099925760,1923874137567332224,1923874142743103360,1923874143068915968,1923874129707206528,1923874124790139136,1923872811100571904,1823878622417027968,1677557350139036928,1630423654964528000,2046348406425518336"    # 去掉电商代发
    # 日期
    start_date, end_date = get_last_month_range()
    print(start_date, end_date)
    # 供应商类型
    vendor_info = _get_vendor_ids_from_db("实销实结,OEM实销实结")
    print(f"用于API的供应商ID (defaultVendId): {vendor_info}")

    # 分页参数初始化
    current_page_index = 0
    page_size = 1000  # 每页请求数量
    all_items_collected = []  # 用于存储所有页面的数据

    # 定义请求URL
    url = "https://web.jackyun.com/jkyun/birc/open/oms/salestatisticsdimgoods/v2"

    # 定义请求头
    headers = {
        "content-length": "495",
        "content-type": "application/x-www-form-urlencoded; charset=UTF-8",
        "module_code": "mainframe",
        "origin": "https://web.jackyun.com",
        "referer": "https://web.jackyun.com/home/mainframe_web.html",
        "sec-ch-ua-mobile": "?0",
        "sec-ch-ua-platform": "Windows",
        "sec-fetch-dest": "empty",
        "sec-fetch-mode": "cors",
        "sec-fetch-site": "same-origin",
        "user-agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.5359.125 Safari/537.36",
        "x-requested-with": "XMLHttpRequest",
    }

    print("开始分页请求销售数据...")
    while True:
        # 定义当前页的请求参数
        payload_data = {
            "timeType": "3",
            "startTime": start_date,
            "endTime": end_date,
            "filterTimeType": "2",
            "companyId": "",
            "isGift": "-1",
            "isSkuStatistic": "1",
            "isAssembly": "2",
            "assemblyDimension": "0",
            "isCancelTrade": "0",
            "excludeFlag": "0",
            "pageIndex": current_page_index,  # 使用动态的页码
            "pageSize": page_size,
            "sortField": "",
            "sortOrder": "",
            "shopId": shopId,
            "defaultVendId": vendor_info,
            "warehouseId": "",
            "cols": '["skuBarcode","goodsNo","goodsName","skuName","unit","brandName","cateName","goodsQty","goodsAmt","grossProfitRate","avgPrice","localCurrencyGoodsAmt","goodsCost","grossProfit","imgUrl","goodsFlagData","defaultVendName","price1","price3","skuGmtCreate"]',
        }

        prepared_payload = getResult1(token, payload_data)

        print(f"正在请求第 {current_page_index + 1} 页数据...")
        try:
            response = requests.post(url, headers=headers, data=prepared_payload, timeout=30)  # 添加超时
            # 检查响应状态
            if response.status_code == 200:
                result_data_json = response.json()
                current_page_items = None

                if isinstance(result_data_json, dict):
                    result_content = result_data_json.get('result')
                    if isinstance(result_content, dict):
                        current_page_items = result_content.get('data')

                if current_page_items and isinstance(current_page_items, list):
                    all_items_collected.extend(current_page_items)
                    print(
                        f"成功获取第 {current_page_index + 1} 页数据，数量: {len(current_page_items)}。当前总数: {len(all_items_collected)}")
                    # 如果返回的数据量小于请求的页面大小，说明是最后一页
                    if len(current_page_items) < page_size:
                        print(
                            f"第 {current_page_index + 1} 页数据量 ({len(current_page_items)}) 小于页面大小 ({page_size})，认定为最后一页。")
                        break
                    current_page_index += 1  # 准备请求下一页
                else:
                    print(f"第 {current_page_index + 1} 页没有有效数据或数据列表为空，停止分页。")
                    break  # 没有更多数据了
            else:
                print(f"请求第 {current_page_index + 1} 页失败，状态码: {response.status_code}")
                print(f"响应内容: {response.text[:500]}...")  # 打印部分错误响应
                break  # 请求失败，停止
        except requests.exceptions.RequestException as e:
            print(f"请求第 {current_page_index + 1} 页时发生网络错误: {e}")
            break  # 网络错误，停止

    # 分页结束后，处理收集到的所有数据
    vendor_groups = {}
    if not all_items_collected:
        print("未能从API获取到任何销售数据。")
    else:
        print(f"总共获取 {len(all_items_collected)} 条销售数据，开始按供应商分组...")
        for item in all_items_collected:
            vendor_name = item.get("defaultVendName", "未知供应商")
            vendor_id = item.get("defaultVendId", "未知ID")
            purchaser = item.get("goodsExtendMap", {}).get("goodsField2", "未知采购员")
            vendor_key = f"{vendor_name}_{vendor_id}"

            if vendor_key not in vendor_groups:
                vendor_groups[vendor_key] = {
                    "vendor_name": vendor_name,
                    "vendor_id": vendor_id,
                    "goodsField2": purchaser,
                    "products": [],
                }
            # 构建商品信息
            product_info = {
                "商品编号": item.get("goodsNo"),
                "商品名称": item.get("goodsName"),
                "SKUid": item.get("skuId"),  # skuId 是 item 的直接子键
                "销量": item.get("goodsQty"),
            }
            vendor_groups[vendor_key]["products"].append(product_info)

    # # 打印分组结果
    # print("\n=== 销售数据按供应商分组统计 ===")
    # if vendor_groups:
    #     print(f"供应商总数: {len(vendor_groups)}家")
    #     print(f"----------------------------------------")
    #     for vendor_key, group_data in vendor_groups.items():  # 将 data 重命名为 group_data 避免冲突
    #         print(f"\n供应商: {group_data['vendor_name']}")
    #         print(f"供应商ID: {group_data['vendor_id']}")
    #         print(f"商品数量: {len(group_data['products'])}个")
    #         print("\n商品明细:")
    #         for product in group_data["products"]:
    #             print(f"- 商品编号: {product['商品编号']}")
    #             print(f"  商品名称: {product['商品名称']}")
    #             print(f"  SKU ID: {product['SKUid']}")
    #             print(f"  销量: {product['销量']}")
    #         print("-" * 50)
    # else:
    #     if all_items_collected:  # 确保如果收集到数据但分组后为空，也给出提示
    #         print("已收集到数据，但未能按供应商成功分组。请检查数据格式或分组逻辑。")
    #     else:
    #         print("没有可供统计的供应商数据。")

    return saveRealSalesVendorTotalInventoryToDB(vendor_groups)

# 把获取的实销实结的本月销量存入数据库中
def saveRealSalesVendorTotalInventoryToDB(vendor_groups):
    """将处理后的实销实结销售货品明细账保存到 dz_real_sales_detail 表"""
    if not vendor_groups:
        print("没有可保存的销售明细数据")
        return False

    conn = None
    cursor = None
    inserted_rows = 0
    try:
        conn = DB()  # 从 common.config 获取数据库连接
        cursor = conn.cursor()

        # 首先清空表
        cursor.execute("DELETE FROM dz_real_sales_detail")
        conn.commit()
        print("已清空 dz_real_sales_detail 表中的所有数据")

        alter_sql = "ALTER TABLE dz_real_sales_detail AUTO_INCREMENT = 1"
        cursor.execute(alter_sql)
        print("已将 dz_real_sales_detail 表的 AUTO_INCREMENT 重置为 1")
        conn.commit()

        sql = """
            INSERT INTO dz_real_sales_detail 
            (purchaser,vendor_id,vendor_name, goods_no, goods_name, sku_id, goodsQty) 
            VALUES (%s,%s, %s, %s, %s, %s, %s)
        """
        
        data_to_insert = []
        for vendor_key, group_data in vendor_groups.items():
            vendor_name_for_db_vend_id = group_data.get("vendor_id") 
            vendor_id_for_db_vend_name = group_data.get("vendor_name")
            purchaser = group_data.get("goodsField2")

            
            for product in group_data.get("products", []):
                goods_no = product.get("商品编号")
                goods_name = product.get("商品名称")
                sku_id = product.get("SKUid")
                goods_qty = product.get("销量")

                # 新增过滤条件：
                # 如果 goodsName 包含 "【DSDF-" 或者 goodsNo 包含 "ds_"，则跳过此商品
                if ("【DSDF-" in goods_name) or ("ds_" in goods_no):
                    print(f"信息: 由于商品名包含 '【DSDF-' 或商品编号包含 'ds_'，跳过保存商品到数据库: 商品名='{goods_name}', 商品编号='{goods_no}'")
                    continue # 继续处理下一个商品

                # 检查数据完整性
                if all([
                    purchaser,
                    vendor_name_for_db_vend_id, 
                    vendor_id_for_db_vend_name, 
                    goods_no, 
                    goods_name, 
                    sku_id is not None, 
                    goods_qty is not None
                ]):
                    # 类型转换以匹配数据库字段类型
                    data_to_insert.append((
                        str(purchaser),  # 对应数据库 purchaser
                        str(vendor_name_for_db_vend_id),  # 对应数据库 vendor_id
                        str(vendor_id_for_db_vend_name),  # 对应数据库 vendor_name
                        str(goods_no), 
                        str(goods_name), 
                        str(sku_id), 
                        float(goods_qty) 
                    ))
                else:
                    print(f"警告: 跳过不完整的商品数据: 供应商Key={vendor_key}, 商品名称={goods_name}, SKU={sku_id}")

        if data_to_insert:
            cursor.executemany(sql, data_to_insert)
            conn.commit()
            inserted_rows = cursor.rowcount 
            print(f"成功将 {inserted_rows} 条销售明细数据保存到 dz_real_sales_detail 表")
            return True
        else:
            print("没有符合过滤条件且数据完整的有效数据可插入数据库。")
            return False
            
    except Exception as e:
        if conn:
            conn.rollback()  # 如果发生错误，回滚事务
        print(f"保存销售明细数据到数据库 dz_real_sales_detail 时出错: {str(e)}")
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()




# 获取全部SKUid，然后查询，然后把查询的结果返回保存到数据库中的一列
# 从供应商货品价目中获取实销实结销售货品单价
def getGoodssource():
    # 获取实销实结供应商的本月货品skuid
    skuId = _get_sku_ids_from_db()
    print(skuId)
    #获取token
    token= get_token_result()
    url = "https://web.jackyun.com/jkyun/erp/goodssource/list"
    header = {
        "content-type": "application/x-www-form-urlencoded; charset=UTF-8",
        "module_code": "mainframe",
        "origin": "https://web.jackyun.com",
        "referer": "https://web.jackyun.com/home/mainframe_web.html",
        "sec-ch-ua-mobile": "?0",
        "sec-ch-ua-platform": "Windows",
        "sec-fetch-dest": "empty",
        "sec-fetch-mode": "cors",
        "sec-fetch-site": "same-origin",
        "user-agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.5359.125 Safari/537.36",
        "x-requested-with": "XMLHttpRequest",
    }

    current_page_index = 0
    page_size = 1000 # 与API请求中的pageSize一致
    all_items_collected = [] # 用于存储所有页面的数据

    print("\n开始分页请求供应商货品价目数据...")
    while True:
        data = {
            "searchFieldName": "goodsNo",
            "searchValue":"",
            "isQuickSearch": "0",
            "vendorId":"",
            "skuId": skuId, # skuId
            "cateIds":"",
            "goodsNoLeft":"",
            "goodsNameLeft":"",
            "skuNameLeft":"",
            "skuBarcodeLeft":"",
            "vendorGoodsNoLeft":"",
            "validStartTime":"",
            "validEndTime":"",
            "goodsBrandLeft":"",
            "currencyCodeLeft":"",
            "isCurrentActive":"1",
            "sourceType":"",
            "gmtCreateStart":"",
            "gmtCreateEnd":"",
            "pageIndex": current_page_index, # 使用动态的页码
            "pageSize": page_size,
            "sortField":"",
            "sortOrder":"",
            "cols": '["priceType", "isCurrentActive", "isDefault", "vendorGoodsNo", "goodsNo", "goodsName", "skuName", "unitName","skuBarcode", "price", "currencyName", "taxRate", "memo", "platSource", "currencyCode", "vendorId", "skuId","sourceNo"]',
            "businessType": "goods.source.by.cols"
        }

        print(f"正在请求第 {current_page_index + 1} 页数据 (pageIndex: {current_page_index}, pageSize: {page_size})...")
        
        try:
            requests_data = getResult1(token,data)
            response = requests.post(url,headers=header,data=requests_data, timeout=30) # 增加超时

            if response.status_code == 200:
                response_data_json = response.json()
                # print(f"  第 {current_page_index + 1} 页原始响应: {json.dumps(response_data_json, ensure_ascii=False, indent=2)}") # 调试用

                current_page_items = response_data_json.get("result", {}).get("data", [])
                
                if isinstance(current_page_items, list) and current_page_items:
                    all_items_collected.extend(current_page_items)
                    print(f"  成功获取第 {current_page_index + 1} 页数据，数量: {len(current_page_items)}。当前总数: {len(all_items_collected)}")
                    
                    if len(current_page_items) < page_size:
                        print(f"  第 {current_page_index + 1} 页数据量 ({len(current_page_items)}) 小于页面大小 ({page_size})，认定为最后一页。")
                        break # 最后一页
                    current_page_index += 1 # 准备请求下一页
                else:
                    print(f"  第 {current_page_index + 1} 页没有有效数据或数据列表为空，停止分页。")
                    if not isinstance(current_page_items, list):
                         print(f"  API返回的'data'字段不是列表类型: {type(current_page_items)}")
                    break # 没有更多数据或数据格式不正确
            else:
                print(f"  请求第 {current_page_index + 1} 页失败，状态码: {response.status_code}")
                print(f"  响应内容: {response.text[:500]}...") # 打印部分错误响应
                break # 请求失败，停止
        except requests.exceptions.Timeout:
            print(f"  请求第 {current_page_index + 1} 页超时。")
            break # 超时，停止
        except requests.exceptions.RequestException as e:
            print(f"  请求第 {current_page_index + 1} 页时发生网络错误: {e}")
            break # 网络错误，停止
        except json.JSONDecodeError as e:
            print(f"  解析第 {current_page_index + 1} 页响应时发生JSON解码错误: {e}")
            print(f"  原始响应文本: {response.text[:500]}...")
            break # JSON解析错误，停止
        
        # 防止无限循环，可以加一个最大页数限制
        if current_page_index > 50: # 假设最多请求50页
            print("  已达到最大请求页数限制，停止分页。")
            break

    print(f"\n分页请求完成。总共从API获取到 {len(all_items_collected)} 条货品明细 (原始数据)。")
    
    if all_items_collected:
        # 打印从API获取的货品明细 (原始数据) - 现在基于 all_items_collected
        filtered_result = []
        skipped_count_isDefault_zero = 0
        for item in all_items_collected: # 使用 all_items_collected 进行过滤
            if item.get("isDefault") == 0:
                skipped_count_isDefault_zero += 1
            else:
                filtered_result.append(item)
        
        if skipped_count_isDefault_zero > 0:
            print(f"\n已从 {len(all_items_collected)} 条原始数据中过滤掉 {skipped_count_isDefault_zero} 条因 'isDefault' 为 0 的记录。")
        
        if filtered_result:
            print(f"将使用 {len(filtered_result)} 条有效货品数据更新数据库价格。")
            # 调用函数更新数据库中的价格，传入过滤后的列表
            update_prices_in_dz_real_sales_detail(filtered_result)
        else:
            print("过滤后没有有效的货品数据可用于更新数据库价格。")
            
    else:
        print("API未返回有效的货品数据（经过分页尝试后）。")
    
    return all_items_collected # 返回所有收集到的数据
# 根据获取到的result，通过vendorId和skuId，确定供应商是哪个供应商的哪个货品，把result中的price更新到dz_real_sales_detail表中的last_purchase_price列中
def update_prices_in_dz_real_sales_detail(goods_data_list):
    """
    根据从API获取的、已预过滤的货品数据列表，更新 dz_real_sales_detail 表中的 last_purchase_price。
    """
    conn = None
    cursor = None
    updated_count = 0
    failed_updates = []

    if not goods_data_list:
        print("没有货品数据可用于更新数据库。 (列表在传入前已为空或被完全过滤)")
        return

    try:
        conn = DB()
        cursor = conn.cursor()
        print(f"\n开始更新数据库 dz_real_sales_detail 表中的 last_purchase_price (基于 {len(goods_data_list)} 条预过滤数据)...")
        for item_index, item in enumerate(goods_data_list):
            vendor_id = item.get("vendorId")
            sku_id = item.get("skuId")
            price = item.get("price")

            # 调试：打印将要用于匹配的ID和价格
            # print(f"  [调试] 尝试更新第 {item_index + 1} 条: API原始vendorId='{vendor_id}', API原始skuId='{sku_id}', price='{price}'")

            if vendor_id is not None and sku_id is not None and price is not None:
                try:
                    price_float = float(price)
                    price_float = round(price_float, 2) # 四舍五入保留两位小数
                    db_vendor_id = str(vendor_id)  # vend_id是VARCHAR(50)类型
                    db_sku_id = int(sku_id)  # sku_id是BIGINT类型，需要转换为整数

                    # 调试：打印转换后用于SQL的ID
                    # print(f"    [调试] SQL条件: vend_id='{db_vendor_id}' (类型: {type(db_vendor_id)}), sku_id='{db_sku_id}' (类型: {type(db_sku_id)}), last_purchase_price={price_float}")
                    # 可选的调试步骤：先查询一下是否存在匹配的记录
                    # check_sql = "SELECT COUNT(*) FROM dz_real_sales_detail WHERE vend_id = %s AND sku_id = %s"
                    # cursor.execute(check_sql, (db_vendor_id, db_sku_id))
                    # count = cursor.fetchone()[0]
                    # print(f"    [调试] 数据库中匹配 (vend_id='{db_vendor_id}', sku_id='{db_sku_id}') 的记录数: {count}")

                    sql = """
                        UPDATE dz_real_sales_detail
                        SET last_purchase_price = %s
                        WHERE vendor_id = %s AND sku_id = %s
                    """
                    cursor.execute(sql, (price_float, db_vendor_id, db_sku_id))
                    rows_affected = cursor.rowcount
                    if rows_affected > 0:
                        updated_count += rows_affected
                        print(f"    成功更新 {rows_affected} 条记录 for vend_id='{db_vendor_id}', sku_id='{db_sku_id}'.")
                    else:
                        # 如果 rows_affected 为 0，但前面的 SELECT COUNT(*) > 0，说明价格可能相同，或者有其他并发问题/触发器等
                        print(
                            f"    未更新记录 for vend_id='{db_vendor_id}', sku_id='{db_sku_id}'. (可能价格未变动或WHERE条件未匹配)")
                except (ValueError, TypeError) as ve:
                    error_msg = f"处理预过滤条目 {item_index+1} 时出错: 供应商ID='{vendor_id}', SKU ID='{sku_id}'. 无效价格 '{price}': {ve}"
                    print(f"  {error_msg}")
                    failed_updates.append({"item": item, "reason": error_msg})
                except Exception as db_e:
                    error_msg = f"数据库更新预过滤条目 {item_index+1} 时出错: 供应商ID='{vendor_id}', SKU ID='{sku_id}', 价格='{price}'. 数据库错误: {db_e}"
                    print(f"  {error_msg}")
                    failed_updates.append({"item": item, "reason": error_msg})
            else:
                error_msg = f"预过滤条目 {item_index+1} 缺少 vendorId, skuId, 或 price. 数据: {item}"
                print(f"  {error_msg}")
                failed_updates.append({"item": item, "reason": error_msg})
        
        conn.commit()
        print(f"\n数据库价格更新完成。总共成功更新 {updated_count} 条记录。")
        if failed_updates:
            print(f"更新过程中出现 {len(failed_updates)} 个问题项，详情如下:")
            for failed_item in failed_updates:
                print(f"  - 条目数据: {failed_item['item']}, 原因: {failed_item['reason']}")

    except Exception as e:
        if conn:
            conn.rollback()
        print(f"更新 dz_real_sales_detail 表时发生严重错误: {str(e)}")
        print("所有更改已回滚。")
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
        print("数据库连接已关闭。")


#实销实结数据抓取总函数
def getRealSales():
    # 1. 获取实销实结销售明细
    _get_SalesDetailList()
    # 2. 从供应商货品价目中获取实销实结销售货品单价
    getGoodssource()


if __name__ == '__main__':
    getRealSales()
